<?php

class Photo extends Model {

    function add($photo) {
        $author_user_id = mysql_real_escape_string($photo["author_user_id"]);
        $title = mysql_real_escape_string($photo["title"]);
        $category = mysql_real_escape_string($photo["category"]);
        $keywords = mysql_real_escape_string($photo["keywords"]);
        $description = mysql_real_escape_string($photo["description"]);
        $date_taken = mysql_real_escape_string($photo["date_taken"]);
        $filename = $photo["filename"];
        $rating = 0;
        $access_rule = mysql_real_escape_string($photo["access_rule"]);
        $comment_rule = mysql_real_escape_string($photo["comment_rule"]);

        $query = "INSERT INTO `photos` (
                    `id`,
                    `author_user_id`,
                    `category`,
                    `keywords`,
                    `description`,
                    `date_taken`,
                    `timestamp`,
                    `rating`,
                    `access_rule`,
                    `comment_rule`,
                    `title`,
                    `filename`
                  )
                  VALUES (
                    NULL,
                    '$author_user_id',
                    '$category',
                    '$description',
                    '$keywords',
                    '$date_taken',
                    CURRENT_TIMESTAMP,
                    '$rating',
                    '$access_rule',
                    '$comment_rule',
                    '$title',
                    '$filename'
                  );";

        $result = $this->query($query);

        return $result;
    }

    function delete($id) {
        $query = "DELETE FROM `photos`
                  WHERE
                    `photos`.`id` = '$id';";

        $result = $this->query($query);

        return $result;
    }

    function get($id, $offset = "0") {
        $query = "SELECT * FROM `photos`
                  WHERE
                    `photos`.`id` = $id;";

        $photo = $this->query($query, true);

        $query = "SELECT
                    `comments`.*,
                    `users`.`name`
                  FROM
                    `comments` JOIN `users`
                  WHERE
                    `comments`.`author_user_id` = `users`.`id`
                    AND
                    `comments`.`type` = 1
                    AND
                    `comments`.`post_id` = $id
                  LIMIT $offset , 5;";

        $comments = $this->query($query);

        $result["Photo"] = $photo["Photo"];
        $result["Comments"] = $comments;

        return $result;
    }

    function get_comments($id, $offset = "0") {
        $query = "SELECT
                    `comments`.*,
                    `users`.`name`
                  FROM
                    `comments` JOIN `users`
                  WHERE
                    `comments`.`author_user_id` = `users`.`id`
                    AND
                    `comments`.`type` = 1
                    AND
                    `comments`.`post_id` = $id
                  ORDER BY
                    `comments`.`id` DESC
                  LIMIT $offset , 5;";

        $comments = $this->query($query);
        
        return $comments;
    }

    function add_comment($comment) {
        $post_id = mysql_real_escape_string($comment["post_id"]);
        $author_user_id = mysql_real_escape_string($comment["author_user_id"]);
        $content = mysql_real_escape_string($comment["content"]);

        $query = "INSERT INTO `comments` (
                    `id`,
                    `type`,
                    `post_id`,
                    `author_user_id`,
                    `timestamp`,
                    `content`
                  )
                  VALUES (
                    NULL,
                    '1',
                    '$post_id',
                    '$author_user_id',
                    CURRENT_TIMESTAMP,
                    '$content'
                  );";

        $result = $this->query($query);

        return $result;
    }

    function delete_comment($id) {
        $query = "DELETE FROM `comments`
                  WHERE
                    `comments`.`id` = $id;";

        $result = $this->query($query);

        return $result;
    }

    function rate($method, $id) {
        if ($method == "increment") {
            $query = "UPDATE `photos`
                  SET
                    `rating` = `rating` + 1
                  WHERE
                    `id` = $id;";
        } else {
            $query = "UPDATE `photos`
                  SET
                    `rating` = `rating` - 1
                  WHERE
                    `id` = $id;";
        }

        $result = $this->query($query);

        return $result;
    }

    function update($photo) {
        $id = $photo["id"];
        $category = $photo["category"];
        $title = $photo["title"];
        $keywords = $photo["keywords"];
        $description = $photo["description"];
        $date_taken = $photo["date_taken"];
        $access_rule = $photo["access_rule"];
        $comment_rule = $photo["comment_rule"];
        $tags = $photo["tags"];

        $query = "UPDATE `photos`
                  SET
                    `category` = '$category',
                    `title` = '$title',
                    `keywords` = '$keywords',
                    `description` = '$description',
                    `date_taken` = '$date_taken',
                    `access_rule` = '$access_rule',
                    `comment_rule` = '$comment_rule'
                  WHERE
                    `id` = $id;";
        
        $result = $this->query($query);

        $query = "DELETE FROM `tags` WHERE `photo_id` = $id";
        $result = $this->query($query);
        
        foreach($tags as $tag):
            if ($tag != 'Empty'):
                $query = "SELECT * FROM `users` WHERE `name` = '$tag'";
                $result = $this->query($query,true);
                $user_id = $result['User']['id'];

                $query = "INSERT INTO `tags` (
                            `photo_id`,
                            `user_id`
                          )
                          VALUES (
                            '$id',
                            '$user_id');";

                $result = $this->query($query);
            endif;
        endforeach;

        return $result;
    }

    function get_user_photos($user_id, $offset = "0") {
        $query = "SELECT * FROM `photos`
                  WHERE
                    `photos`.`author_user_id` = '$user_id'
                  LIMIT $offset , 7;";

        $result = $this->query($query);

        return $result;
    }

    function add_tag($id) {
        $query = "SELECT `users`.*
                  FROM `users` JOIN `relations`
                  WHERE
                    `users`.`id` = `relations`.`following_user_id` AND
                    `relations`.`user_id` = $id";

        $result['following'] = $this->query($query);

        $query = "SELECT * FROM `users` WHERE `users`.`id` = $id";

        $result['user'] = $this->query($query);
        
        return $result;
    }

    function tag($photo_id) {
        $query = "SELECT
                    `tags`.*,
                    `users`.`name`
                  FROM `tags` JOIN `users`
                  WHERE
                    `tags`.`user_id` = `users`.`id` AND
                    `tags`.`photo_id` = $photo_id";

        $result = $this->query($query);
        return $result;
    }

    function update_tag($photo_id, $tags) {
        $query = "DELETE FROM `tags`
                  WHERE
                    `tags`.`photo_id` = $photo_id";

        $result = $this->query($query);
        foreach($tags as $tag):
            $user_id = $tag;
            $query = "INSERT INTO `tags` (
                        `photo_id`,
                        `user_id`
                      )
                      VALUES (
                        '$photo_id',
                        '$user_id'
                      );";

            $result = $this->query($query);
        endforeach;
    }

    function select_newest($offset = "0") {
        $query = "SELECT *
                  FROM
                    `photos`
                  ORDER BY
                    `photos`.`id` DESC
                  LIMIT $offset , 21";

        return $this->query($query);
    }

    function select_popular($offset = "0") {
        $query = "SELECT * FROM
                    (SELECT * FROM
                        `photos`
                        JOIN
                        (SELECT
                            `comments`.`post_id`,
                            COUNT(`comments`.`post_id`) AS `comment_count`
                        FROM
                            `comments`
                        WHERE
                            `comments`.`type` = '1'
                        GROUP BY `comments`.`post_id`) AS `comments`
                        ) AS `photos`
                    WHERE
                        `photos`.`id` = `photos`.`post_id`
                  ORDER BY
                        `photos`.`comment_count` DESC
                  LIMIT $offset , 21";

        return $this->query($query);
    }

    function select_highest($offset = "0") {
        $query = "SELECT *
                  FROM
                    `photos`
                  ORDER BY
                    `photos`.`rating` DESC
                  LIMIT $offset , 21";

        return $this->query($query);
    }

    function add_notification($user_id, $photo) {
        $query = "SELECT * FROM `users`
                  WHERE
                    `id` = $user_id";

        $result = $this->query($query,true);
        $name = $result['User']['name'];
        $id = $result['User']['id'];
        $photo_id = $photo["id"];

        foreach($photo["tags"] as $tag):
            if ($tag != 'Empty'):
                $query = "SELECT * FROM `users` WHERE `name` = '$tag'";
                $result = $this->query($query,true);
                $user_id = $result['User']['id'];

                $query = "INSERT INTO `notifications` (
                        `author_user_id`,
                        `type`,
                        `user_id`,
                        `user_name`,
                        `photo_id`
                      )
                      VALUES (
                        $user_id,
                        2,
                        $id,
                        '$name',
                        $photo_id
                    )";

            $result = $this->query($query);
            endif;
        endforeach;
    }

    function restore_photo($id) {
        $query = "UPDATE `users`
                  SET
                    `photo_id` = 1
                  WHERE
                    `id` = $id;";

        $result = $this->query($query);
    }

    function user_photos($user_id) {
        $query = "SELECT * FROM `photos` JOIN `users`
                  WHERE
                    `users`.`photo_id` = `photos`.`id` AND
                    `users`.`id` = '$user_id';";

        $result = $this->query($query, true);

        return $result;
    }
}
